﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HotelSoft.HotelManagement_DTO;
using System.Collections;
using System.Data;

namespace HotelSoft.HotelManagement_DAO
{
    class PhieuDangKyDAO:AbstractDAO
    {
        public override bool Them(object AbstractDTO)
        {
            try
            {
                PhieuDangKyDTO dto = (PhieuDangKyDTO)AbstractDTO;
                string insertCommand = "INSERT INTO PHIEUDANGKY VALUES('" +
                   dto.SoPDK + "', '" +
                   dto.SoNguoi + "','" +
                   dto.Ngayden.ToString("MM/dd/yyyy") + "', '" +
                   dto.SoNgayThue + "', '" +
                   dto.TraTruoc + "', N'" + dto.ChuThich + "', '" +
                   dto.SoPhong + "')";
                Config.AccessHelper.ExecuteQuery(insertCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool UpdateluuSoPDK(object AbstractDTO)
        {
            try
            {
                Luu_PhieuDangKyDTO dto = (Luu_PhieuDangKyDTO)AbstractDTO;
                string updateCommand = "UPDATE Luu_PHIEUDANGKY SET  SoPDK = '" + dto.ISoPDK + "' ";
                Config.AccessHelper.ExecuteQuery(updateCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static DataTable SelectAll()
        {
            string sql = "SELECT * FROM PHIEUDANGKY Where ChuThich = N'Chưa thanh toán'";
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public static DataTable SelectphieudangkytheoSoPDK(object AbstractDTO)
        {
            PhieuDangKyDTO dto = (PhieuDangKyDTO)AbstractDTO;
            string sql = "SELECT * FROM PHIEUDANGKY Where SoPDK='" + dto.SoPDK + "'";
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public static int LaydulieuSoPDK()
        {
            int sopdk=0;
            //string strim;
            string sql = "SELECT * FROM PHIEUDANGKY";
            DataTable ds = Config.AccessHelper.ExecuteQuery(sql);
            int iSoDong = ds.Rows.Count;
            string SoPDK = ds.Rows[iSoDong-1]["SoPDK"].ToString();
            sopdk = int.Parse(SoPDK.ToString());
            sopdk++;
            

            return sopdk;

        }
        public static DataTable UpdatephieudangkytheoSoPDK(object AbstractDTO)
        {
            PhieuDangKyDTO dto = (PhieuDangKyDTO)AbstractDTO;
            string updateCommand = "UPDATE PHIEUDANGKY SET  ChuThich = N'Thanh toán' WHERE SoPDK = '" + dto.SoPDK+"'";
            return Config.AccessHelper.ExecuteQuery(updateCommand);
        }

        public ArrayList LayDanhSachPhieuDangKy()
        {
            ArrayList arrDanhSach = new ArrayList();
            string sql = "Select*from PHIEUDANGKY";
            DataTable ds = Config.AccessHelper.ExecuteQuery(sql);
            PhieuDangKyDTO hd;
            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                hd = new PhieuDangKyDTO();
                hd.SoPDK = ds.Rows[i]["SoPDK"].ToString();
                hd.SoNguoi = int.Parse(ds.Rows[i]["SoNguoi"].ToString());
                hd.Ngayden = Convert.ToDateTime(ds.Rows[i]["Ngayden"].ToString());
                hd.SoNgayThue = int.Parse(ds.Rows[i]["SoNgayThue"].ToString());
                hd.TraTruoc = float.Parse(ds.Rows[i]["TraTruoc"].ToString());
                hd.ChuThich = ds.Rows[i]["ChuThich"].ToString();
                hd.SoPhong = ds.Rows[i]["SoPhong"].ToString();
                arrDanhSach.Add(hd);
                i++;
            }
            return arrDanhSach;
        }
        public override bool CapNhatThongTin(object AbstractDTO)
        {
            return false;
        }
        public override void Xoa(object AbstractDTO)
        {
            try
            {
                PhieuDangKyDTO dto = (PhieuDangKyDTO)AbstractDTO;
                string deleteCommand = "DELETE FROM PHIEUDANGKY WHERE SoPDK = '" + dto.SoPDK + "'";
                Config.AccessHelper.ExecuteNonQuery(deleteCommand);
                return;
            }
            catch
            {
                return;
            }
        }

        internal void Xoa(ArrayList dsPhieuDangKy)
        {
            foreach (PhieuDangKyDTO PDK in dsPhieuDangKy)
            {
                Xoa(PDK);
            }
        }
        public override void TraCuu(object AbstractDTO)
        {

        }

        public bool UpdateNgayThue(object AbstractDTO)
        {
            try
            {
                PhieuDangKyDTO dto = (PhieuDangKyDTO)AbstractDTO;
                string updateCommand = "UPDATE PHIEUDANGKY SET SoNgayThue = " + dto.SoNgayThue + " WHERE SoPDK = '" + dto.SoPDK + "'";
                Config.AccessHelper.ExecuteQuery(updateCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static DataTable CapNhatThongTindondulieu(object AbstractDTO)
        {
            PhieuDangKyDTO dto = (PhieuDangKyDTO)AbstractDTO;
            string updateCommand = "UPDATE PHIEUDANGKY SET SoNguoi = '" + dto.SoNguoi + "', Ngayden = '" + dto.Ngayden.ToString("MM/dd/yyyy") + "', SoNgayThue = '" + dto.SoNgayThue + "', TraTruoc = '" + dto.TraTruoc + "' WHERE SoPDK = '" + dto.SoPDK + "'";
            return Config.AccessHelper.ExecuteQuery(updateCommand);
        }

        public ArrayList getlistPDK_Tracuu(object AbstractDTO)
        {
            ArrayList arrDanhSach = new ArrayList();
            PhieuDangKyDTO dto=(PhieuDangKyDTO)AbstractDTO;
            string sql = "Select*from PHIEUDANGKY where SoPDK='"+dto.SoPDK+"'";
            DataTable ds = Config.AccessHelper.ExecuteQuery(sql);
            PhieuDangKyDTO hd;
            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                hd = new PhieuDangKyDTO();
                hd.SoPDK = ds.Rows[i]["SoPDK"].ToString();
                hd.SoNguoi = int.Parse(ds.Rows[i]["SoNguoi"].ToString());
                hd.Ngayden = Convert.ToDateTime(ds.Rows[i]["Ngayden"].ToString());
                hd.SoNgayThue = int.Parse(ds.Rows[i]["SoNgayThue"].ToString());
                hd.TraTruoc = float.Parse(ds.Rows[i]["TraTruoc"].ToString());
                hd.ChuThich = ds.Rows[i]["ChuThich"].ToString();
                hd.SoPhong = ds.Rows[i]["SoPhong"].ToString();
                arrDanhSach.Add(hd);
                i++;
            }
            return arrDanhSach;
        }

        public DataTable getInfo_report(object AbstractDTO)
        {
            PhieuDangKyDTO dto = (PhieuDangKyDTO)AbstractDTO;
            string str = "Select p.SoPDK, SoNguoi, Ngayden, SoNgayThue, Tratruoc, SoPhong, ChuThich, k.CMND,  HoTenKH, NgaySinh, GioiTinh, DiaChi, QuocTich, SDTKH "+
                        "From KHACHHANG k, PHIEUDANGKY p, KHACHHANG_PHIEUDANGKY pk "+
                        "Where p.SoPDK=pk.SoPDK and pk.CMND=k.CMND and p.SoPDK='"+dto.SoPDK+"'";
            return Config.AccessHelper.ExecuteQuery(str);
        }

    }
}
